import hashlib
import sqlite3
import time
from pathlib import Path


class DataBase:
    def __init__(self):
        self.path = Path(Path(__file__).parent, "users.db")
        self.db = sqlite3.connect(self.path)

    def creat_if(self):  # 判断表和数据库是否存在
        try:
            self.db.execute("SELECT USERNAME FROM USER_GUI WHERE ID=1;")
            print("数据库存在，请继续")
        except:
            print("数据库不存在，已新建")
            sql = """
            CREATE TABLE USER_GUI(
                ID INTEGER NOT NULL PRIMARY KEY,
                USERNAME TEXT NOT NULL UNIQUE,
                PASSWORD TEXT NOT NULL,
                SIGN_UP_TIME TIMESTAMP DEFAULT NULL,
                LOCK TEXT NOT NULL DEFAULT 'FALSE',
                LOCKTIME TIMESTAMP DEFAULT NULL,
                PASSWORD_FALSE_COUNT INTEGER DEFAULT 0
            );"""
            self.db.execute(sql)
            self.db.commit()

    def insert(self, username, password):  # 插入新记录
        password_md5 = hashlib.md5("add_salt".encode(encoding="utf-8"))  # 加盐增加安全性
        password_md5.update(password.encode(encoding="utf-8"))
        sql = """
            INSERT INTO
                USER_GUI (USERNAME, PASSWORD, SIGN_UP_TIME)
            VALUES
                ('{}', '{}', '{}')
        """.format(
            username,
            password_md5.hexdigest(),
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
        )
        self.db.execute(sql)
        self.db.commit()

    def select(self, username):  # 查询
        sql = """
            SELECT
                *
            FROM
                USER_GUI
            WHERE
                USERNAME = '{}'
        """.format(
            username
        )
        user_info = self.db.execute(sql)
        return user_info.fetchone()

    def update_lock(self, username):  # 锁定该用户
        sql = """
            UPDATE
                USER_GUI
            SET
                LOCK = 'TRUE',
                LOCKTIME = '{}'
            WHERE
                USERNAME = '{}'
        """.format(
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), username
        )
        self.db.execute(sql)
        self.db.commit()

    def update_unlock(self, username):  # 解锁
        sql = """
            UPDATE
                USER_GUI
            SET
                LOCK = 'FALSE',
                LOCKTIME = NULL,
                PASSWORD_FALSE_COUNT = 0
            WHERE
                USERNAME = '{}'
        """.format(
            username
        )
        self.db.execute(sql)
        self.db.commit()

    def update_password_false_count(self, username, password_false_count):  # 更新累计输错次数
        sql = """
            UPDATE
                USER_GUI
            SET
                PASSWORD_FALSE_COUNT = {}
            WHERE
                USERNAME = '{}'        
        """.format(
            password_false_count, username
        )
        self.db.execute(sql)
        self.db.commit()

    def close_db(self):  # 关闭连接
        self.db.close()


if __name__ == "__main__":
    db = DataBase()  # 实例化，用于测试此脚本

    # test-1: 数据库是否存在
    db.creat_if()
    print("测试1：完成。请检查是否已有【user.db】文件")

    # test-2: 插入新用户
    try:
        username = "test"
        password = "123456789"
        db.insert(username, password)
        print("测试2：完成。已成功插入新用户数据")
    except:
        print("测试2：完成。数据库中已有用户【test】")

    # test-3: 查询
    select = db.select(username)
    print("测试3：完成。查询结果为：{}".format(select))

    # test-4: 锁定
    db.update_lock(username)
    print("测试4：完成。查询结果为：{}".format(db.select(username)))

    # test-5: 解锁
    db.update_unlock(username)
    print("测试5：完成。查询结果为：{}".format(db.select(username)))

    # test-6: 写入输错密码的次数
    password_false_count = 1
    db.update_password_false_count(username, password_false_count)
    print("测试6：完成。查询结果为：{}".format(db.select(username)))
